package com.financing.controller.report;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.financing.controller.user.UserController;
import com.financing.model.Data;
import com.financing.model.report.SqlReport;
import com.financing.service.report.SqlReportService;
import com.financing.util.DateUtil;
import com.financing.util.Json;
import com.financing.util.Str;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class SqlReportController extends Controller {

	private SqlReportService sqlReport = new SqlReportService();
	private static final Logger logger = Logger.getLogger(UserController.class);
	
	/**
	 *  搜索数据
	 */
	public void search() {
		int id = getParaToInt("id",0);
		SqlReport sql =SqlReport.me.findById(id);
		String sqlStr = sql.getContents();
		int page = getParaToInt("page",1);
		int size = getParaToInt("size",10);
		
		if(sql!=null && Str.nb(sqlStr)) {
			String params = sql.getParams();
			String where = "";
			if(Str.nb(params)) {
				JSONArray array = JSONArray.parseArray(params);
				for(int i = 0; i<array.size(); i++) {
					JSONObject obj = (JSONObject) array.get(i);
					if(Str.nb(getPara(obj.getString("field")))) {
						where += Str.replace(obj.getString("contents"), "?", "'"+getPara(obj.getString("field"))+"'");
					}
				}
			}
			sqlStr = Str.replaceOnce(sqlStr, "?", Str.nb(where)?where:"");
			logger.error("sql1==="+sqlStr.split("#")[0]);
			logger.error("sql2==="+sqlStr.split("#")[1]);
			renderJson(Json.ok("数据获取成功！", Data.put("sql",sql,"list", Db.paginate(page, size, sqlStr.split("#")[0], sqlStr.split("#")[1]))));
		}else{
			renderJson(Json.error("数据不存在！"));
		}
	}
	
	/**
	 *  数据下载
	 * @throws IOException 
	 */
	public void download() throws IOException {
		int id = getParaToInt("id",0);
		SqlReport sql =SqlReport.me.findById(id);
		String sqlStr = sql.getContents();
		if(sql!=null && Str.nb(sqlStr)) {
			String params = sql.getParams();
			String where = "";
			if(Str.nb(params)) {
				JSONArray array = JSONArray.parseArray(params);
				for(int i = 0; i<array.size(); i++) {
					JSONObject obj = (JSONObject) array.get(i);
					if(Str.nb(getPara(obj.getString("field")))) {
						where += Str.replace(obj.getString("contents"), "?", "'"+getPara(obj.getString("field"))+"'");
					}
				}
			}
			sqlStr = Str.replaceOnce(sqlStr, "?", Str.nb(where)?where:"");
			logger.error("sql1==="+sqlStr.split("#")[0]);
			logger.error("sql2==="+sqlStr.split("#")[1]);
			
			// 创建HSSFWorkbook对象(excel的文档对象)  
			HSSFWorkbook wb = new HSSFWorkbook();  
			// 建立新的sheet对象（excel的表单）  
			HSSFSheet sheet = wb.createSheet(sql.getName());  
			// 设置第一行标题内容
			HSSFRow row1=sheet.createRow(0);  
			String[] titles = sql.getTitles().split(",");
			for(int i=0; i<titles.length; i++) {
				HSSFCell c1=row1.createCell(i);  
				c1.setCellValue(titles[i]);  
				sheet.setColumnWidth(i,6000);
			}
			
			// 设置详细内容
			int index = 1;
			Page<Record> records = Db.paginate(1, 99999, sqlStr.split("#")[0], sqlStr.split("#")[1]);
			for(Record r : records.getList()) {
				HSSFRow row=sheet.createRow(index++);
				int i = 0;
				for(String key : sql.getTitles().split(",")) {
					Object columnObj = r.getColumns().get(key);
					String val = columnObj!=null && Str.nb(columnObj.toString()) ? columnObj.toString() : "" ;
					HSSFCell c1= row.createCell(i++); 
					c1.setCellValue(val);   
				}
			}
			
		    // 输出Excel文件  
			HttpServletResponse response = getResponse();
		    OutputStream output = response.getOutputStream();  
		    response.reset();  
		    response.setHeader("Content-disposition", "attachment; filename="+DateUtil.getTimeStamp()+".xls");  
		    response.setContentType("application/msexcel");          
		    wb.write(output);  
		    output.close();  
		    renderNull();
		    
		}else{
			renderJson(Json.error("数据不存在！"));
		}
	}
	
	/**
	 *  获取帮助列表
	 */
	public void list() {
		int page = getParaToInt("page",1);
		int size = getParaToInt("size",10);
		renderJson(Json.ok("数据获取成功！", sqlReport.list(page, size, Data.flat(getParaMap()))));
	}
	
	/**
	 * 编辑帮助信息
	 */
	public void edit() {
		int id = getParaToInt("id",0);
		SqlReport sql = id>0 ? SqlReport.me.findById(id) : new SqlReport();
		sql.setName(getPara("name"));
		sql.setTitles(getPara("titles"));
		sql.setContents(getPara("contents"));
		sql.setSummary(getPara("summary"));
		sql.setParams(getPara("params"));
		
		if(id>0 ? sql.update() : sql.setCreateTime(new Date()).save()){
			renderJson(Json.ok("报表信息编辑成功！"));
		}else{
			renderJson(Json.error("报表信息编辑失败！"));
		}
	}
}
 